package com.zhida84.Dao;
/**
 * 
 * @author shazhengbo sunchangxing
 *
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBSQL {
	private final String DRIVER = "com.mysql.jdbc.Driver";
	private final String URL = "jdbc:mysql://127.0.0.1:3306/zdbs?useUnicode=true&characterEncoding=utf8";
	private final String USER = "root";
	private final String PWD = "123456";
	private Connection conn = null;
	private Statement stmt = null;
	protected ResultSet rs = null;
	private PreparedStatement pstmt = null;

	public DBSQL() {
	}

	/**
	 * ������
	 */
	public Connection getConn() {
		if (conn == null) {
			getConnection();
		}
		return conn;
	}

	/**
	 * ���ݿ����ӷ���,���Statement����
	 * 
	 */
	public void getConnection() {

		try {
			Class.forName(DRIVER);
			conn = DriverManager.getConnection(URL, USER, PWD);
			stmt = conn.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}// �����ݿ�
	}

	/**
	 * ���ݿ��ѯ���������ResultSet
	 * 
	 */
	public ResultSet query(String strSql) {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(strSql);
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return rs;
		}
	}

	/**
	 * ���ݿ��ѯ���������� ��ѯԤ������󣬻��ResultSet����
	 * 
	 */
	public ResultSet query(PreparedStatement pstmt) {
		this.pstmt = pstmt;
		ResultSet rs = null;
		try {
			rs = this.pstmt.executeQuery();
			return rs;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return rs;
		}
	}

	/**
	 * ���ݿ���·���������Ӱ������
	 * 
	 */
	public int executeSql(String strSql) {
		int result = 0;
		try {
			stmt = conn.createStatement();
			result = stmt.executeUpdate(strSql);
		} catch (SQLException e) {
			e.printStackTrace();
			result = -1;
		}
		return result;
	}

	/**
	 * ���ݿ���·��������ò�ѯԤ������󣬷�����Ӱ������
	 * 
	 */
	public int executeSql(PreparedStatement pstmt) {
		int result = 0;
		this.pstmt = pstmt;
		try {
			result = this.pstmt.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return -1;
		}
		return result;
	}

	/**
	 * �ر�����
	 * 
	 */
	public void closeConnection() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * ��ѯ�ĵײ㷽��
	 * 
	 * @param sql
	 *            Ҫִ�е����
	 * @param arr
	 *            Ҫ��ѯ������
	 * @return RS �����
	 */

	public ResultSet getQuery(String sql, Object[] arr) {
		getConnection();// ��ȡ����
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			if (arr != null && arr.length > 0) {
				for (int i = 0; i < arr.length; i++) {
					psmt.setObject(i + 1, arr[i]);// ע��psmt��1��ʼ
				}
			}
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	/**
	 * ��ҳ����
	 */
	public ResultSet getPageQuery(String sql,String [] arr){
		getConnection();// ��ȡ����
		try {
			PreparedStatement psmt = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			if (arr != null && arr.length > 0) {
				for (int i = 0; i < arr.length; i++) {
					psmt.setString(i + 1, arr[i]);// ע��psmt��1��ʼ
				}
			}
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	

	/**
	 * ��ɾ��
	 * 
	 * @param SQL
	 * @param arr
	 * @return ��Ӱ�������
	 */
	public int getUpdate(String sql, Object[] arr) {
		int result = 0;
		getConnection();
		try {
			PreparedStatement psmt = conn.prepareStatement(sql);
			if (arr != null && arr.length > 0) {
				for (int i = 0; i < arr.length; i++) {
					psmt.setObject(i + 1, arr[i]);
				}
			}
			result = psmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

}
